﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DataLayer
{
    public class dlinsertproduct
    {
        public void InsertProduct(string name)
        {
            string mijnconnectiestring;
            string status = "not started";

            mijnconnectiestring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

            SqlConnection conn = new SqlConnection(mijnconnectiestring);

            // might have to  before insert

            conn.Open();

            string insertString = "insert into Product (Name, Status) values (@Name, @Status)";

            SqlCommand Query = new SqlCommand(insertString, conn);

            Query.Parameters.AddWithValue("@Name", name);
            Query.Parameters.AddWithValue("@Status", status);

            Query.ExecuteNonQuery();
            conn.Close();  
        }

        public void InsertSprint(string titel, string description, DateTime begin, DateTime end, int productid, int teamid)
        {
            string mijnconnectiestring;

            mijnconnectiestring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

            SqlConnection conn = new SqlConnection(mijnconnectiestring);

            // might have to  before insert

            conn.Open();
            
            string insertString = "insert into Sprints (Titel, Description, BeginDate, EstimatedEndDate, ProductId, TeamId) values (@Titel, @Description, @BeginDate, @EndDate, @ProductId, @TeamId)";

            SqlCommand Query = new SqlCommand(insertString, conn);

            Query.Parameters.AddWithValue("@Titel", titel);
            Query.Parameters.AddWithValue("@Description", description);
            Query.Parameters.AddWithValue("@BeginDate", begin);
            Query.Parameters.AddWithValue("@EndDate", end);
            Query.Parameters.AddWithValue("@ProductId", productid);
            Query.Parameters.AddWithValue("@TeamId", teamid);

            Query.ExecuteNonQuery();
            conn.Close();  
        }

        public void InsertTask(string titel, string description, int estimated, int userid, int userstoryid)
        {
            string mijnconnectiestring;
            string status = "Not Started";

            mijnconnectiestring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

            SqlConnection conn = new SqlConnection(mijnconnectiestring);

            // might have to  before insert

            conn.Open();
            
            string insertString = "insert into Tasks (Titel, Description, Status, EstimatedWorkload, userId, userstoryId) values (@Titel, @Description, @Status, @EstimatedWorkload, @UserId, @UserstoryId)";

            SqlCommand Query = new SqlCommand(insertString, conn);

            Query.Parameters.AddWithValue("@Titel", titel);
            Query.Parameters.AddWithValue("@Description", description);
            Query.Parameters.AddWithValue("@Status", status);
            Query.Parameters.AddWithValue("@EstimatedWorkload", estimated);
            Query.Parameters.AddWithValue("@UserId", userid);
            Query.Parameters.AddWithValue("@UserstoryId", userstoryid);

            Query.ExecuteNonQuery();
            conn.Close();  
        }

        public DataSet UserStoryTeam(int userstoryid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string querystring = @"select Sprints.TeamId from UserStory inner join Sprints on 
UserStory.SprintID = Sprints.Sprintid where UserStoryId = " + userstoryid + "";

            SqlConnection connection = new SqlConnection(connStr);
            SqlCommand querylogin = new SqlCommand(querystring, connection);

            SqlDataAdapter myadapter = new SqlDataAdapter(querylogin);
            DataSet mydataset = new DataSet();

            myadapter.Fill(mydataset);

            return mydataset;

        }
    }
}
